package cn.e4j.modular.system.service.impl;

import cn.e4j.core.dto.Dtos;
import cn.e4j.core.dto.impl.Dto;
import cn.e4j.core.shiro.ShiroKit;
import cn.e4j.core.shiro.ShiroUser;
import cn.e4j.core.util.AppCxt;
import cn.e4j.core.util.DateUtils;
import cn.e4j.modular.system.entity.SysTableColumn;
import cn.e4j.modular.system.mapper.SysTableColumnMapper;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.collections.map.HashedMap;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 *  表结构 服务实现类
 * </p>
 *
 * @author miaoyl
 * @since 2019-10-21
 */
@Service
public class SysTableColumnServiceImpl extends ServiceImpl<SysTableColumnMapper, SysTableColumn> {

    @Transactional(readOnly = true)
    public List<Map<String, Object>> getListData(Page<Object> page, Dto dto) {
        List<Map<String, Object>> listData = this.baseMapper.getTableList(page, dto);
        return listData;
    }

    @Transactional
    public Dto addData(List<SysTableColumn> tableColumnsList, List<Map<String, Object>> tableDto) {
        Dto outDto = Dtos.newDto();
        outDto.setAppCode(200);
        outDto.setAppMsg("表结构新增成功！");
        ShiroUser shiroUser = ShiroKit.getUser();
        String ERP = AppCxt.getParam("ERP");
        try {
            //创建表格
            /*List<Dto> createList = new ArrayList<>();
            List<Dto> keyList = new ArrayList<>();
            List<Dto> defaultList = new ArrayList<>();
            List<Map<String, Object>> columnList = new ArrayList<>();
            for (int i = 0; i < tableColumnsList.size(); i++) {
                SysTableColumn tableColumn = tableColumnsList.get(i);
                Dto aDto = Dtos.newDto();
                aDto.put("COLUMN_ID", tableColumn.getCOLUMN_ID());
                aDto.put("COLUMN_TYPE", tableColumn.getCOLUMN_TYPE());
                aDto.put("COLUMN_LENGTH", tableColumn.getCOLUMN_LENGTH());
                aDto.put("IS_NULL", "Y".equals(tableColumn.getIS_NULL()) ? "NULL" : "NOT NULL");
                aDto.put("DEAFAULT_VALUE", tableColumn.getDEAFAULT_VALUE());
                createList.add(aDto);
                if(tableColumn.getDEAFAULT_VALUE() != null && !("").equals(tableColumn.getDEAFAULT_VALUE())){
                    Dto bDto = Dtos.newDto();
                    String dfName = "DF_"+ tableColumn.getTABLE_ID()+"_"+tableColumn.getCOLUMN_ID();
                    bDto.put("COLUMN_ID", tableColumn.getCOLUMN_ID());
                    bDto.put("DFNAME", dfName);
                    bDto.put("DEAFAULT_VALUE", tableColumn.getDEAFAULT_VALUE());
                    bDto.put("TABLE_ID", tableColumn.getTABLE_ID());
                    defaultList.add(bDto);
                }
                if ("Y".equals(tableColumn.getIS_PRIMARY_KEY())) {
                    Dto cDto = Dtos.newDto();
                    cDto.put("COLUMN_ID", tableColumn.getCOLUMN_ID());
                    keyList.add(cDto);
                }
            }*/

            if("T100".equals(ERP)){
                //创建表
                Dto createDto = Dtos.newDto();
                createDto.put("TABLE_ID", tableDto.get(0).get("TABLE_ID"));
                //createDto.put("tableList", createList);
                createDto.put("tableList", tableDto);
                this.baseMapper.createTableO(createDto);

                //添加字段注释
                for(Map<String, Object> map : tableDto){
                    if(map.get("COLUMN_NAME") != null && !("".equals(map.get("COLUMN_NAME")))){
                        String sql = "COMMENT ON column " + map.get("TABLE_ID") + "." + map.get("COLUMN_ID") + " is ''" + map.get("COLUMN_NAME")+ "''";
                        //this.baseMapper.addColumnNameO(map);
                        map.put("sql",sql);
                        this.baseMapper.addColumnNameO(map);
                    }
                }

                //添加表注释
                if(tableDto.get(0).get("TABLE_ID") != null && !("".equals(tableDto.get(0).get("TABLE_ID")))){
                    this.baseMapper.addTableNameO(tableDto.get(0));
                }
            }else {
                //创建表
                Dto createDto = Dtos.newDto();
                createDto.put("TABLE_ID", tableDto.get(0).get("TABLE_ID"));
                //createDto.put("tableList", createList);
                createDto.put("tableList", tableDto);
                this.baseMapper.createTable(createDto);

                //添加默认值
                /*if(defaultList.size() > 0){
                    for(int i=0;i < defaultList.size();i++){
                        Dto defaultDto = defaultList.get(i);
                        String sql = "ALTER TABLE " + defaultDto.getString("TABLE_ID")+" ADD CONSTRAINT " + defaultDto.getString("DFNAME")
                                +" default(''"+ defaultDto.getString("DEAFAULT_VALUE")+"'') FOR "
                                + defaultDto.getString("COLUMN_ID");
                        Map<String, Object> map = new HashedMap();
                        map.put("sql", sql);
                        this.baseMapper.addDefault(map);
                    }
                }*/
                //添加字段注释
                for(Map<String, Object> map : tableDto){
                    if(map.get("COLUMN_NAME") != null && !("".equals(map.get("COLUMN_NAME")))){
                        this.baseMapper.addColumnName(map);
                    }
                }

                //添加表注释
                if(tableDto.get(0).get("TABLE_ID") != null && !("".equals(tableDto.get(0).get("TABLE_ID")))){
                    this.baseMapper.addTableName(tableDto.get(0));
                }
            }


            //添加主键
            List<Map<String, Object>> keyList = new ArrayList<>();
            keyList.addAll(tableDto);
            keyList = keyList.stream()
                    .filter(w1 -> w1.get("IS_PRIMARY_KEY").equals("Y")).collect(Collectors.toList());
            if (keyList.size() > 0) {
                Dto keyDto = Dtos.newDto();
                keyDto.put("TABLE_ID", tableColumnsList.get(0).getTABLE_ID());
                keyDto.put("KEYNAME", "PK_" + tableColumnsList.get(0).getTABLE_ID());
                keyDto.put("keyList", keyList);
                this.baseMapper.addPrimary(keyDto);
            }

            //增加新的明细
            for (int i = 0; i < tableColumnsList.size(); i++) {
                SysTableColumn tableColumn = tableColumnsList.get(i);
                tableColumn.setCREATE_BY(shiroUser.getID());
                tableColumn.insert();
            }

            SysTableColumn sysTableColumn = new SysTableColumn();
            sysTableColumn.setCREATE_DATE(LocalDateTime.now());
            UpdateWrapper<SysTableColumn> sysTableColumnUpdateWrapper = new UpdateWrapper<>();
            sysTableColumnUpdateWrapper.eq("TABLE_ID", tableColumnsList.get(0).getTABLE_ID());
            sysTableColumn.update(sysTableColumnUpdateWrapper);
        }catch (Exception e){
            //手动回滚
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            try{
                Dto dropDto = new Dto();
                dropDto.put("TABLE_ID", tableDto.get(0).get("TABLE_ID"));
                this.baseMapper.dropTable(dropDto);
            }catch (Exception ex){
                outDto.setAppCode(500);
                outDto.setAppMsg(e.getMessage());
            }
            outDto.setAppCode(500);
            outDto.setAppMsg(e.getMessage());
        }
        return outDto;
    }

    @Transactional
    public void delete(SysTableColumn sysTableColumn) {
        Dto inDto = Dtos.newDto();
        inDto.put("TABLE_ID", sysTableColumn.getTABLE_ID());
        Dto count = this.getTableCount(inDto);
        if("Y".equals(count.getString("ISEXSIT"))){
            this.baseMapper.dropTable(inDto);
        }
        UpdateWrapper<SysTableColumn> sysTableColumnUpdateWrapper = new UpdateWrapper<>();
        sysTableColumnUpdateWrapper.eq("TABLE_ID", sysTableColumn.getTABLE_ID());
        sysTableColumn.delete(sysTableColumnUpdateWrapper);
    }

    @Transactional
    public void delDataBatch(List<SysTableColumn> sysTableColumnList) {
        for (SysTableColumn sysTableColumn : sysTableColumnList) {
            Dto inDto = Dtos.newDto();
            inDto.put("TABLE_ID", sysTableColumn.getTABLE_ID());
            Dto count = this.getTableCount(inDto);
            if("Y".equals(count.getString("ISEXSIT"))){
                this.baseMapper.dropTable(inDto);
            }
            //this.baseMapper.dropTable(inDto);
            UpdateWrapper<SysTableColumn> sysTableColumnUpdateWrapper = new UpdateWrapper<>();
            sysTableColumnUpdateWrapper.eq("TABLE_ID", sysTableColumn.getTABLE_ID());
            sysTableColumn.delete(sysTableColumnUpdateWrapper);
        }
    }

    @Transactional(readOnly = true)
    public List<Map<String, Object>> getTableData(Dto dto) {
        List<Map<String, Object>> listData = this.baseMapper.getTableData(dto);
        return listData;
    }

    //修改表格
    @Transactional
    public Dto editData(List<SysTableColumn> tableColumnsList, List<Map<String, Object>> tableDto) {
        Dto outDto = Dtos.newDto();
        outDto.setAppCode(200);
        outDto.setAppMsg("表结构修改成功！");
        String ERP = AppCxt.getParam("ERP");
        try {
            ShiroUser shiroUser = ShiroKit.getUser();
            //查询表格数据
            Dto inDto = Dtos.newDto();
            inDto.put("TABLE_ID", tableColumnsList.get(0).getTABLE_ID());
            List<Map<String, Object>> oldList = this.baseMapper.getOldData(inDto); //未修改前的数据
            inDto.put("type", "Y");
            List<Map<String, Object>> typeList = this.baseMapper.getOldData(inDto); //未修改前的数据

            //sameList = tableDto∩oldList
            List<Map<String, Object>> sameList = new ArrayList<>();
            tableDto.stream().forEach(w -> sameList.addAll(oldList.stream()
                    .filter(w1 -> w1.get("COLUMN_ID").equals(w.get("COLUMN_ID")) || w1.get("COLUMN_ID").equals(w.get("ORIGINAL_VALUE"))).collect(Collectors.toList())));

            //删除字段
            List<Map<String, Object>> delList = new ArrayList<>();
            delList.addAll(oldList);
            delList.removeAll(sameList);

            //新增字段tableDto-sameList
            List<Map<String, Object>> addList = new ArrayList<>();
            addList.addAll(tableDto);
            sameList.stream().forEach(w -> addList.removeAll(addList.stream()
                    .filter(w1 -> w1.get("COLUMN_ID").equals(w.get("COLUMN_ID")) || (w1.get("ORIGINAL_VALUE") != null && w1.get("ORIGINAL_VALUE").equals(w.get("COLUMN_ID"))))
                    .collect(Collectors.toList())));

            //修改字段
            List<Map<String, Object>> editList = new ArrayList<>();
            editList.addAll(tableDto);
            editList.removeAll(addList);
            editList = editList.stream()
                    .filter(w1 -> w1.get("FLAG").equals("Y")).collect(Collectors.toList());

            //key值修改
            List<Map<String, Object>> oldkeyList = new ArrayList<>();
            oldkeyList.addAll(oldList);
            oldkeyList = oldkeyList.stream()
                    .filter(w -> w.get("IS_PRIMARY_KEY").equals("Y"))
                    .collect(Collectors.toList());

            List<Map<String, Object>> keyList = new ArrayList<>();
            keyList.addAll(tableDto);
            keyList = keyList.stream()
                    .filter(w -> w.get("IS_PRIMARY_KEY").equals("Y")).collect(Collectors.toList());



            if("T100".equals(ERP)){
                //删除原本键值约束
                Dto delDto = new Dto();
                if(delList.size() > 0 || editList.size() > 0 || editList.size() >0){
                    if (oldkeyList.size() > 0) {
                        delDto.put("TABLE_ID", oldkeyList.get(0).get("TABLE_ID"));
                        delDto.put("KEYNAME", "PK_" + oldkeyList.get(0).get("TABLE_ID"));
                        String sql = "ALTER TABLE "+ oldkeyList.get(0).get("TABLE_ID")+" DROP primary key";
                        delDto.put("sql",sql);
                        this.baseMapper.dropKeyO(delDto);
                    }
                }

                //删除字段
                if (delList.size() > 0) {
                    for (int i = 0; i < delList.size(); i++) {
                        Map<String, Object> map = delList.get(i);
                        this.baseMapper.dropColumn(map);
                    }
                }

                //新增字段
                if (addList.size() > 0) {
                    for (int i = 0; i < addList.size(); i++) {
                        Map<String, Object> map = addList.get(i);
                        //this.baseMapper.addColumnO(map);
                        //添加字段注释
                        String sql = "ALTER TABLE "+ map.get("TABLE_ID")+" ADD "+ map.get("COLUMN_ID")+" " + map.get("COLUMN_TYPE");
                        if(map.get("COLUMN_LENGTH") != null && !("".equals(map.get("COLUMN_LENGTH")))){
                            sql = sql+ "("+ map.get("COLUMN_LENGTH");
                            if(map.get("COLUMN_SCALE") != null && !("".equals(map.get("COLUMN_SCALE")))){
                                sql = sql+","+ map.get("COLUMN_SCALE");
                            }
                            sql = sql+") ";
                        }
                        if(map.get("DEAFAULT_VALUE") != null && !("".equals(map.get("DEAFAULT_VALUE")))){
                            sql = sql+"DEFAULT ''"+ map.get("DEAFAULT_VALUE") + "''";
                        }
                        if(("Y".equals(map.get("IS_NULL")))){
                            sql = sql + " NULL ";
                        }else if(("N".equals(map.get("IS_NULL")))){
                            sql = sql + " NOT NULL ";
                        }
                        map.put("sql", sql);
                        this.baseMapper.addColumnO(map);
                        if (map.get("COLUMN_NAME") != null && !("".equals(map.get("COLUMN_NAME")))) {
                            String sql_name = "COMMENT ON column " + map.get("TABLE_ID") + "." + map.get("COLUMN_ID") + " is ''" + map.get("COLUMN_NAME")+ "''";
                            map.put("sql",sql_name);
                            this.baseMapper.addColumnNameO(map);
                        }
                    }
                }

                //修改字段
                if (editList.size() > 0) {
                    for (int i = 0; i < editList.size(); i++) {
                        Map<String, Object> map = editList.get(i);
                        if (!map.get("COLUMN_ID").equals(map.get("ORIGINAL_VALUE"))) {
                            String sql = "alter table "+ map.get("TABLE_ID")+" rename column "+ map.get("ORIGINAL_VALUE")+" to "+ map.get("COLUMN_ID");
                            map.put("sql",sql);
                            this.baseMapper.editColumnIDO(map);
                        }
                        this.baseMapper.editColumnO(map);
                        //修改字段注释
                        String sql = "COMMENT ON column " + map.get("TABLE_ID") + "." + map.get("COLUMN_ID") + " is ''" + map.get("COLUMN_NAME")+ "''";
                        //this.baseMapper.addColumnNameO(map);
                        map.put("sql",sql);
                        this.baseMapper.addColumnNameO(map);
                    }
                }

                //修改表注释
                this.baseMapper.addTableNameO(tableDto.get(0));

                //插入新的键值约束
                if(delList.size() > 0 || editList.size() > 0 || editList.size() >0) {
                    if (keyList.size() > 0) {
                        delDto.put("TABLE_ID", keyList.get(0).get("TABLE_ID"));
                        delDto.put("KEYNAME", "PK_" + keyList.get(0).get("TABLE_ID"));
                        delDto.put("keyList", keyList);
                        this.baseMapper.addPrimary(delDto);
                    }
                }

            }else {
                inDto.put("typeList", typeList);
                List<Map<String, Object>> defaultList = this.baseMapper.getDefaultKey(inDto); //默认值约束

                Dto delDto = new Dto();
                if(delList.size() > 0 || addList.size() > 0 || editList.size() >0){
                    if (oldkeyList.size() > 0) {
                        delDto.put("TABLE_ID", oldkeyList.get(0).get("TABLE_ID"));
                        delDto.put("KEYNAME", "PK_" + oldkeyList.get(0).get("TABLE_ID"));
                        this.baseMapper.dropKey(delDto);
                    }
                }

                //删除字段
                if (delList.size() > 0) {
                    for (int i = 0; i < delList.size(); i++) {
                        Map<String, Object> map = delList.get(i);
                        //删除默认值约束
                        if (map.get("DEAFAULT_VALUE") != null || !map.get("DEAFAULT_VALUE").equals("")) {
                            List<Map<String, Object>> DList = new ArrayList<>();
                            DList.addAll(defaultList);
                            DList = DList.stream()
                                    .filter(w -> w.get("COLUMN_ID").equals(map.get("COLUMN_ID"))).collect(Collectors.toList());
                            if (DList.size() > 0) {
                                //map.put("DEFAULT_KEY", "DF_" + map.get("TABLE_ID")+"_" + map.get("COLUMN_ID"));
                                map.put("DEFAULT_KEY", DList.get(0).get("KEYID"));
                                this.baseMapper.dropDefaultValue(map);
                            }
                        }
                        this.baseMapper.dropColumn(map);
                    }
                }

                //新增字段
                if (addList.size() > 0) {
                    for (int i = 0; i < addList.size(); i++) {
                        Map<String, Object> map = addList.get(i);
                        this.baseMapper.addColumn(map);
                        //添加字段注释
                        if (map.get("COLUMN_NAME") != null && !("".equals(map.get("COLUMN_NAME")))) {
                            this.baseMapper.addColumnName(map);
                        }
                    }
                }

                //修改字段
                if (editList.size() > 0) {
                    for (int i = 0; i < editList.size(); i++) {
                        Map<String, Object> map = editList.get(i);
                        //删除默认值约束
                        List<Map<String, Object>> DList = new ArrayList<>();
                        DList.addAll(defaultList);
                        DList = DList.stream()
                                .filter(w -> w.get("COLUMN_ID").equals(map.get("ORIGINAL_VALUE"))).collect(Collectors.toList());
                        if (DList.size() > 0) {
                            /*if(map.get("DEFAULT_KEY_old") != null && !"".equals(map.get("DEFAULT_KEY_old"))){*/
                                    //map.put("DEFAULT_KEY", "DF_" + map.get("TABLE_ID")+"_" + map.get("COLUMN_ID"));
                                    map.put("DEFAULT_KEY", DList.get(0).get("KEYID"));
                                    this.baseMapper.dropDefaultValue(map);
                            /*}*/
                        }
                        if (!map.get("COLUMN_ID").equals(map.get("ORIGINAL_VALUE"))) {
                            this.baseMapper.editColumnID(map);
                        }
                        this.baseMapper.editColumn(map);
                        if (!"".equals(map.get("DEAFAULT_VALUE"))) {
                    /*String CONSTRAINT_NAME = "DF_" + map.get("TABLE_ID")+"_" + map.get("COLUMN_ID");
                    String sql = "ALTER TABLE " + map.get("TABLE_ID")+" ADD CONSTRAINT " + CONSTRAINT_NAME +" default(''"+ map.get("DEAFAULT_VALUE")+"'') FOR " + map.get("COLUMN_ID");*/
                            String sql = "ALTER TABLE " + map.get("TABLE_ID") + " ADD default(''" + map.get("DEAFAULT_VALUE") + "'') FOR " + map.get("COLUMN_ID");
                            map.put("sql", sql);
                            this.baseMapper.addDefault(map);
                        }
                        if (map.get("COLUMN_NAME_old") != null) {
                            if ("".equals(map.get("COLUMN_NAME_old")) && !"".equals(map.get("COLUMN_NAME"))) {//添加字段注释
                                this.baseMapper.addColumnName(map);
                            } else if (!"".equals(map.get("COLUMN_NAME_old"))) {//修改字段注释
                                if (map.get("COLUMN_NAME") != null && !"".equals(map.get("COLUMN_NAME"))) {
                                    this.baseMapper.editColumnName(map); //修改
                                } else {
                                    this.baseMapper.dropColumnName(map); //删除
                                }
                            }
                        }
                    }
                }

                //修改表注释
                if (!oldList.get(0).get("TABLE_NAME").equals(tableDto.get(0).get("TABLE_NAME"))) {
                    if (oldList.get(0).get("TABLE_NAME") == null || oldList.get(0).get("TABLE_NAME").equals("")) {
                        this.baseMapper.addTableName(tableDto.get(0));
                    } else {
                        this.baseMapper.editTableName(tableDto.get(0));
                    }
                }

                //插入新的键值约束
                if(delList.size() > 0 || addList.size() > 0 || editList.size() >0) {
                    if (keyList.size() > 0) {
                        delDto.put("TABLE_ID", keyList.get(0).get("TABLE_ID"));
                        delDto.put("KEYNAME", "PK_" + keyList.get(0).get("TABLE_ID"));
                        delDto.put("keyList", keyList);
                        this.baseMapper.addPrimary(delDto);
                    }
                }

            }

            //清空表数据
            SysTableColumn delSysTableColumn = new SysTableColumn();
            UpdateWrapper<SysTableColumn> delSysTableColumnUpdateWrapper = new UpdateWrapper<>();
            delSysTableColumnUpdateWrapper.eq("TABLE_ID", tableColumnsList.get(0).getTABLE_ID());
            delSysTableColumn.delete(delSysTableColumnUpdateWrapper);

            //修改表格数据
            for (int i = 0; i < tableColumnsList.size(); i++) {
                SysTableColumn tableColumn = tableColumnsList.get(i);
                tableColumn.setCREATE_BY(shiroUser.getID());
                tableColumn.insert();
            }

            SysTableColumn sysTableColumn = new SysTableColumn();
            sysTableColumn.setCREATE_DATE(LocalDateTime.now());
            UpdateWrapper<SysTableColumn> sysTableColumnUpdateWrapper = new UpdateWrapper<>();
            sysTableColumnUpdateWrapper.eq("TABLE_ID", tableColumnsList.get(0).getTABLE_ID());
            sysTableColumn.update(sysTableColumnUpdateWrapper);
        }catch (Exception e){
            //手动回滚
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            outDto.setAppCode(500);
            outDto.setAppMsg(e.getMessage());
        }
        return outDto;
    }

    @Transactional(readOnly = true)
    public Dto getTableCount(Dto dto) {
        Dto outDto = new Dto();
        try {
            outDto = this.baseMapper.getTableCount(dto);
            outDto.put("ISEXSIT", "Y");
        }catch (Exception e){
            //e.printStackTrace();
            //outDto = new Dto();
            outDto.put("NUM", 0);
            outDto.put("ISEXSIT", "N");
        }
        return outDto;
    }

}
